﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.Common;
using System.Diagnostics;
using System.Xml.Linq;
using System.Net;
using System.Net.NetworkInformation;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using EtlComm;

namespace Etltools
{
    public partial class FrmCmp : Form
    {
        string strconnnect = "";
        string provider = "";
        string stationid = "";
        string stationip = "";

        public string strLocalIP = "";
        public string username = "";
        public string password = "";

        DbDataAdapter ada;
        DbConnection conn;

        IList<innerSQL> lstSQL = new List<innerSQL>();

        class innerSQL
        {
            public string sql { set; get; }
            public string result { get; set; }
            public bool HasParam { get; set; }
        }

        public FrmCmp()
        {
            InitializeComponent();
        }

        private void btnx_query_Click(object sender, EventArgs e)
        {
            if (!IsHostActived())
            {
                MessageBox.Show(string.Format("主机{0}不是活动的。",stationip));
                return;
            }
             DataTable dt = GetIBTable(tbx_QueryString.Text);

            dataGridViewX1.DataSource = dt;
            labelX1.Text = string.Format("共 {0} 条", dt.Rows.Count);
           
            //GetProviderFactoryClasses();
        }

        private bool IsHostActived()
        {
            using (Ping p = new Ping())
            {
                PingReply pr = p.Send(stationip);
                if (pr.Status == IPStatus.Success)
                    return true;
                return false;
            }
        }

        private DataTable GetIBTable(string strsqlment)
        {
            Debug.Assert(!string.IsNullOrEmpty(strsqlment));
            Debug.Assert(comboBoxEx1.SelectedIndex != -1);

            strconnnect = string.Format(@"DriverName=Interbase;Database={0}:c:\\office\\DB\\office.gdb;RoleName=RoleName;User_Name=sysdba;Password=masterkey;SQLDialect=3;MetaDataAssemblyLoader=Borland.Data.TDBXInterbaseMetaDataCommandFactory,Borland.Data.DbxReadOnlyMetaData,Version=11.0.5000.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b;GetDriverFunc=getSQLDriverINTERBASE;LibraryName=dbxint30.dll;VendorLib=GDS32.DLL", stationip);
            provider = "Borland.Data.AdoDbxClient";

            string strSQL = strsqlment;
            if (!cbx_detail.Checked)
                strSQL = strsqlment.Replace("*", "count(*)");

            DataTable dt = GetDataTable(provider, strconnnect, strSQL);
            return dt;
        }
        private DataTable GetDataTable(string providerName, string connectionString, string queryString)
        {
            DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);

            DbConnection connection = factory.CreateConnection();
            conn = connection;
            connection.ConnectionString = connectionString;
            //using (connection)
            //{
                // Create the select command.
                DbCommand command = factory.CreateCommand();
                command.CommandText = queryString;
                command.Connection = connection;

                // Create the DbDataAdapter.
                DbDataAdapter adapter = factory.CreateDataAdapter();
                ada = adapter;
                adapter.SelectCommand = command;

                //// Create the DbCommandBuilder.
                //DbCommandBuilder builder = factory.CreateCommandBuilder();
                //builder.DataAdapter = adapter;

                //// Get the insert, update and delete commands.
                //adapter.InsertCommand = builder.GetInsertCommand();
                //adapter.UpdateCommand = builder.GetUpdateCommand();
                //adapter.DeleteCommand = builder.GetDeleteCommand();

                // Fill the DataTable.
                DataTable table = new DataTable();
                adapter.Fill(table);
                return table;
            //}
        }

        static DataTable GetProviderFactoryClasses()
        {
            // Retrieve the installed providers and factories.
            DataTable table = DbProviderFactories.GetFactoryClasses();

            // Display each row and column value.
            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn column in table.Columns)
                {
                    Console.WriteLine(string.Format("{0}:{1}", column.ColumnName, row[column]));
                }
            }
            return table;
        }

        private void btnx_query_mssql_Click(object sender, EventArgs e)
        {
             DataTable dt = GetMSSQLTable(tbx_QueryString.Text);

            dataGridViewX2.DataSource = dt;
            labelX2.Text = string.Format("共 {0} 条", dt.Rows.Count);
        }

        private DataTable GetMSSQLTable(string strsqlment)
        {
            Debug.Assert(!string.IsNullOrEmpty(strsqlment));
            Debug.Assert(comboBoxEx1.SelectedIndex != -1);

            strconnnect = string.Format("Data Source={0};Initial Catalog=OilStationDataTrans;User ID={1};Password={2}", strLocalIP, username, password);
            provider = "System.Data.SqlClient";

            string strSQL = "";
            if (strsqlment.ToLower().Contains("where"))
                strSQL = strsqlment + string.Format(" and stationid = '{0}'", stationid);
            else
                strSQL = strsqlment + string.Format(" where stationid = '{0}'", stationid);

            if (!cbx_detail.Checked)
                strSQL = strSQL.Replace("*", "count(*)");

            DataTable dt = GetDataTable(provider, strconnnect, strSQL);
            return dt;
        }
        private void FrmCmp_Load(object sender, EventArgs e)
        {
            comboBoxEx1.ValueMember = "id";
            comboBoxEx1.DisplayMember = "name";
            comboBoxEx1.Items.AddRange((this.Tag as IList<Station>).ToArray());
        }

        private void comboBoxEx1_SelectedIndexChanged(object sender, EventArgs e)
        {
            stationip = (comboBoxEx1.Items[comboBoxEx1.SelectedIndex] as Station).ibip;
            stationid = (comboBoxEx1.Items[comboBoxEx1.SelectedIndex] as Station).id;
            labelX3.Text = "加油站编码：" + stationid.ToString();
            lstSQL.Clear();
            XDocument xdoc = XDocument.Load("TaskInfo.xml");
            XElement xeTaskinfo = xdoc.Element("TaskInfo");
            var y = from a in xeTaskinfo.Descendants("Task")
                    select new
                    {
                        taskid = a.Element("TaskID").Value,
                        sql = a.Element("SQLStatement").Value,
                        hasparam = a.Element("HaveParams").Value == "1"
                    };

            xdoc = XDocument.Load("StationTaskRunInfo.xml");
            XElement xeStation = xdoc.Element("StationInfo");
            var z = from a in xeStation.Descendants("Station")
                    select new
                    {
                        stationid = a.Element("StationID").Value,
                        task = (from b in a.Descendants("TaskInfo")
                                select new
                                {
                                    taskid = b.Element("TaskID").Value,
                                    paramstart = b.Element("StartTime").Value,
                                    paramend = b.Element("EndTime").Value,
                                })
                    };
            var x = (from a in z
                     where a.stationid == stationid.ToString()
                     select a).First().task;
            
            foreach (var t in x)
            {
                foreach (var s in y)
                {
                    if (t.taskid == s.taskid)
                        if (s.hasparam)
                            lstSQL.Add(new innerSQL { sql = string.Format(s.sql, t.paramstart, t.paramend),HasParam = true });
                        else
                            lstSQL.Add(new innerSQL { sql = s.sql,HasParam = false });
                }
            }
            dataGridViewX3.DataSource = lstSQL;
            dataGridViewX3.Refresh();
        }

        private void btnx_compare_Click(object sender, EventArgs e)
        {
            try
            {
                cbx_detail.Checked = false;
                foreach (var x in lstSQL)
                {
                    DataTable dt1 = GetMSSQLTable(x.sql);
                    DataTable dt2 = GetIBTable(x.sql);

                    if (!cbx_detail.Checked)
                    {
                        if (Convert.ToInt32(dt1.Rows[0][0]) >= Convert.ToInt32(dt2.Rows[0][0]))
                        {
                            x.result = "比对成功";
                        }
                        else
                            x.result = "错误";
                    }
                    dt1.Dispose();
                    dt2.Dispose();
                    
                    dataGridViewX3.Refresh();
                    Application.DoEvents();
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
        }

        private void dataGridViewX3_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            tbx_QueryString.Text = dataGridViewX3.CurrentCell.Value.ToString();
        }

        private void btn_BatchUpdate_Click(object sender, EventArgs e)
        {
            DataTable ibdt = GetIBTable(tbx_QueryString.Text);
            DataTable msdt = GetMSSQLTable(tbx_QueryString.Text);
            ibdt.Columns.Add(new DataColumn("StationID", typeof(string)) { DefaultValue = stationid });
            string tabName = ParseTnameFromQuery(tbx_QueryString.Text);
            ActionUpdate(ibdt, (SqlConnection)conn, tabName);
        }

        private string ParseTnameFromQuery(string strQuery)
        {
            string[] ArrWord = strQuery.ToLower().Split(new char[]{' '},StringSplitOptions.RemoveEmptyEntries);
            int index = Array.FindIndex(ArrWord, (x => x.Equals("from")));
            return ArrWord[index+1];
        }

        private void ActionUpdate(System.Data.DataTable result, SqlConnection c, string DestTableName)
        {
            Debug.Assert(result != null);

            DataTable destTable = new DataTable();
            SqlDataAdapter ada = null;
            SqlCommandBuilder scbBuilder = null;
            
            string sql1 = "";

            sql1 = "select * from " + DestTableName + " where 1=1 ";
            string[] pks = tb_keys.Text.Split(new char[]{','},StringSplitOptions.RemoveEmptyEntries);
            foreach (var pk in pks)
            {
                sql1 += string.Format(" and {0} in (", pk);
                for (int i = 0; i < result.Rows.Count; i++)
                {
                    sql1 += string.Format("'{0}',", result.Rows[i][pk]);
                }
                sql1 = sql1.Remove(sql1.Length - 1);
                sql1 += ")";
            }

            using (SqlCommand sqlcmd = new SqlCommand())
            {
                sqlcmd.CommandText = sql1;
                sqlcmd.Connection = c;
                ada = new SqlDataAdapter(sqlcmd);
                ada.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                scbBuilder = new SqlCommandBuilder(ada);
                if (c.State == ConnectionState.Closed)
                    c.Open();
                ada.Fill(destTable);
                //c.Close();
            }

            if (destTable.PrimaryKey.Count() == 0)
            {
                MessageBox.Show("primarykey is null");
                ada.Dispose();
                destTable.Dispose();
                return;
            }

            foreach (DataRow dr in result.Rows)
            {
                IList<object> lstpk = new List<object>();
                foreach (var pkey in destTable.PrimaryKey)
                    lstpk.Add(dr[pkey.ColumnName]);
                DataRow kkdr = destTable.Rows.Find(lstpk.ToArray());
                if (kkdr == null)
                {
                    //把StationID转换到最前面来
                    int drcount = dr.ItemArray.Count();
                    object[] temp = new object[drcount];
                    temp[0] = dr.ItemArray[drcount - 1];
                    Array.Copy(dr.ItemArray, 0, temp, 1, drcount - 1);

                    destTable.Rows.Add(temp);
                    continue;
                }
                foreach (DataColumn x in destTable.Columns)
                    if (!destTable.PrimaryKey.Contains(x))
                        kkdr[x.ColumnName] = dr[x.ColumnName];
            }
            //foreach (DataRow dr in addtable.Rows)
            //{

            //}

            ada.Update(destTable);
            ada.Dispose();
            destTable.Dispose();

            #region 老的UPDATE
            //for (int i = 0; i < result.Rows.Count; i++)
            //{
            //    string sql = string.Format("update {0} set ", DestTableName);
            //    using (SqlCommand sqlcmd = new SqlCommand())
            //    {
            //        sqlcmd.Connection = c;
            //        foreach (var npk in nopks)
            //        {
            //            sql += string.Format(" {0}='{1}' ", npk.Name, result.Rows[i][npk.Name]);
            //        }
            //        sql += sqlwhere;
            //        foreach (var pk in pks)
            //        {
            //            sql += string.Format(" and {0}='{1}' ", pk.Name, result.Rows[i][pk.Name]);
            //        }
            //        if (sql.EndsWith(sqlwhere))
            //        {
            //            continue;
            //        }
            //        sqlcmd.CommandText = sql;
            //        if (c.State == ConnectionState.Closed)
            //            c.Open();
            //        int ret = sqlcmd.ExecuteNonQuery();
            //        if (ret != 1)
            //            lstRet.Add(i);
            //        c.Close();
            //    }
            //}
            //if (lstRet.Count != 0)
            //{
            //    DataTable addtable = result.Clone();
            //    foreach (var idx in lstRet)
            //    {
            //        addtable.Rows.Add(result.Rows[idx]);
            //    }
            //    ActionAdd(addtable, c, mapCol, DestTableName);
            //} 
            #endregion
        }

        private string PrepareSQLStatement(System.Data.DataTable result, IDictionary<string, TableKey> mapCol)
        {
            var PrimaryKeyColl = tb_keys.Text.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            StringBuilder sbSql = new StringBuilder();
            foreach (var pk in PrimaryKeyColl)
            {
                if (pk.Equals(EtlGlobal.SID, StringComparison.CurrentCultureIgnoreCase))
                {
                    sbSql.AppendFormat(" and {0} = '{1}' ", pk, result.Rows[0][pk]);
                    continue;
                }
                //string[] buf = new string[result.Rows.Count];
                //for (int i = 0; i < result.Rows.Count; i++)
                //{
                //    buf[i] = result.Rows[i][pk.Name].ToString();
                //}
                var buf = result.AsEnumerable().Select(f => f.Field<string>(pk)).ToArray();
                var bufDist = buf.Distinct();
                if (bufDist.Count() == 1)
                {
                    sbSql.AppendFormat(" and {0} = '{1}' ", pk, bufDist.First());
                }
                else
                {
                    var maxValue = bufDist.Max(x => int.Parse(x));
                    var minValue = bufDist.Min(x => int.Parse(x));
                    sbSql.AppendFormat(" and {0} between {1} and {2} ", pk, minValue, maxValue);
                }
            }
            return sbSql.ToString();
        }

        private void ActionAdd(System.Data.DataTable result, SqlConnection c, IDictionary<string, TableKey> mapCol, string DestTableName)
        {
            Debug.Assert(result != null);
            if (result.Rows.Count == 0)
                return;
            if (c.State == ConnectionState.Closed)
                c.Open();
            using (SqlBulkCopy DesBulkOp = new SqlBulkCopy(c, SqlBulkCopyOptions.UseInternalTransaction, null))
            {
                DesBulkOp.DestinationTableName = DestTableName;
                DesBulkOp.BulkCopyTimeout = 60 * 50;
                foreach (var objmap in mapCol)
                {
                    DesBulkOp.ColumnMappings.Add(objmap.Key, objmap.Value.Name);
                }
                DesBulkOp.WriteToServer(result);
            }
        }

        private void ActionDelete(System.Data.DataTable result, SqlConnection c, IDictionary<string, TableKey> mapCol, string DestTableName)
        {
            Debug.Assert(result != null);
            if (result.Rows.Count == 0)
                return;
            if (c.State == ConnectionState.Closed)
                c.Open();

            using (SqlCommand sqlcmd = new SqlCommand())
            {
                sqlcmd.CommandTimeout = 60 * 5;
                sqlcmd.Connection = c;
                sqlcmd.CommandText = string.Format("select count(*) from {0} where {1}= '{2}'", DestTableName, EtlGlobal.SID, result.Rows[0][EtlGlobal.SID]);
                int rnum = (int)sqlcmd.ExecuteScalar();
                if (rnum == result.Rows.Count)
                    sqlcmd.CommandText = string.Format("delete {0} where {1}= '{2}'", DestTableName, EtlGlobal.SID, result.Rows[0][EtlGlobal.SID]);
                else
                    sqlcmd.CommandText = string.Format("delete {0} where 1=1 {1}", DestTableName, PrepareSQLStatement(result, mapCol));
                Debug.WriteLine("---- " + sqlcmd.CommandText);
                sqlcmd.ExecuteNonQuery();
            }
            c.Close();
        }
    }
}
